package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.heaven.cons.Roles;
import cn.ztydata.teachingsystem.heaven.entity.Role;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.List;
import java.util.Objects;
import java.util.Set;

/**
 * 角色Dao
 *
 * @author fanruiping
 * @since 2014-12-01
 */
@Repository
public class RoleDao extends BaseDao {
    /**
     * 新增授权
     * @param entity 实体类
     * @return Boolean
     *
     * @author fanruiping
     * @since 2014-12-02
     */
    public Boolean add(Role entity){
        String sql = "INSERT INTO t_teacher_role(iTeacherId,tiRole,iDeptId,iSpecialtyId) VALUES(:teacherId," +
                ":role,:deptId,:specialtyId)";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("teacherId",entity.getTeacherId()).addValue("role",entity.getRole())
                .addValue("deptId", entity.getDeptId()).addValue("specialtyId", entity.getSpecialtyId());

        log.debug("add user authorize");

        return namedParameterJdbcTemplate.update(sql, params) == 1;
    }

    /**
     * 删除授权
     * @param teacherId
     * @param roleId
     * @param deptId
     * @param specialtyId
     * @return int
     *
     * @author fanruiping
     * @since 2014-12-03
     */
    public int delete(Long teacherId, int roleId, Long deptId, Long specialtyId){
        String sql = "DELETE FROM t_teacher_role WHERE iTeacherId=:teacherId AND tiRole=:roleId AND iDeptId=:deptId AND iSpecialtyId=:specialtyId";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("teacherId", teacherId).addValue("roleId", roleId)
                .addValue("deptId", deptId).addValue("specialtyId", specialtyId);

        log.debug("delete authorize");

        return namedParameterJdbcTemplate.update(sql, params);
    }

    /**
     * 验证授权是否已存在
     * @param entity 实体类
     * @return Boolean
     *
     * @author fanruiping
     * @since 2014-12-01
     */
    public Boolean checkIsExist(Role entity){
        String sql = "";

        //判断是否是超级管理员或院级用户
        if(Roles.SUPER_ADMIN == entity.getRole() || Roles.COLLEGE_USER == entity.getRole()){
            log.debug("user role is super admin or collage user");

            sql = "SELECT COUNT(id) FROM t_teacher_role WHERE iTeacherId=:teacherId AND tiRole=:role LIMIT 1";
        }else if(Roles.DEPT_ADMIN == entity.getRole()){//判断是否是系部管理员
            log.debug("user role is department admin");

            sql = "SELECT COUNT(id) FROM t_teacher_role WHERE iTeacherId=:teacherId AND tiRole=:role AND iDeptId=:deptId LIMIT 1";
        }else{//专业负责人
            log.debug("user role is specialty leader");

            sql = "SELECT COUNT(id) FROM t_teacher_role WHERE tiRole=:role AND iSpecialtyId=:specialtyId LIMIT 1";
        }

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("teacherId",entity.getTeacherId()).addValue("role",entity.getRole())
                .addValue("deptId", entity.getDeptId()).addValue("specialtyId", entity.getSpecialtyId());

        log.debug("return check authorize is exist result");

        return namedParameterJdbcTemplate.queryForObject(sql, params, Integer.class) > 0;
    }

    /**
     * 验证用户是否拥有指定角色
     * @param teacherId 教师编号
     * @param role 角色
     * @return Boolean
     *
     * @author fanruiping
     * @since 2014-12-03
     */
    public Boolean checkRole(long teacherId, int role){
        String sql = "SELECT COUNT(id) FROM t_teacher_role WHERE iTeacherId=:teacherId AND tiRole=:role LIMIT 1";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("teacherId", teacherId).addValue("role", role);

        log.debug("用户是否拥有指定角色");

        return namedParameterJdbcTemplate.queryForObject(sql, params, Integer.class) >= 1;
    }

    /**
     * 授权列表
     * @param roleId 角色编号
     * @param deptId 系部编号
     * @param specialtyId 专业编号
     * @return List
     *
     * @author fanruiping
     * @since 2014-12-11
     */
    public List<Role> getAuthorisedList(int roleId, Long deptId, Long specialtyId){
        String sql = "SELECT id,iDeptId,tiRole,iSpecialtyId,iTeacherId FROM t_teacher_role WHERE tiRole=:roleId AND " +
                "iDeptId=:deptId AND iSpecialtyId=:specialtyId";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("roleId", roleId).addValue("deptId", deptId)
                .addValue("specialtyId", specialtyId);

        log.debug("获取授权用户列表");

        return namedParameterJdbcTemplate.query(sql, params,CreateMapper());
    }

    /**
     * 根据教师编号和角色编号获取数据集合
     * @param teacherId 教师编号
     * @param roleId 角色编号
     * @return List
     *
     * @author fanruiping
     * @since 2014-12-29
     */
    public Set<Long> getByTeacherId(Long teacherId,int roleId){
        final Set<Long> ids = new HashSet<>();

        String sql = "SELECT iDeptId FROM t_teacher_role WHERE iTeacherId=:teacherId AND tiRole=:roleId";

        log.debug("return role list");

        namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("teacherId", teacherId).addValue("roleId", roleId), new RowMapper<Object>() {
            @Override
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                ids.add(rs.getLong("iDeptId"));

                return null;
            }
        });

        return ids;
    }


    /**
     * 根据教师编号和角色编号获取专业ID
     * @param teacherId 教师ID
     * @param roleId 角色ID
     * @return int
     * @author lyj
     * @since 2015-2-9
     */
    public List<Long> getSpecIdByRoleId(Long teacherId, int roleId) {
        String sql = "SELECT iSpecialtyId FROM t_teacher_role WHERE iTeacherId=:teacherId AND tiRole=:roleId";

        MapSqlParameterSource param = new MapSqlParameterSource()
                .addValue("roleId", roleId)
                .addValue("teacherId", teacherId);

        log.debug("根据教师编号和角色编号获取专业ID");
        return namedParameterJdbcTemplate.queryForList(sql, param, Long.class);

    }

    /**
     * ResultSet转换为Role实体（部分数据）
     * @return RowMapper
     *
     * @author fanruiping
     * @since 2014-12-02
     */
    private RowMapper<Role> CreateMapper(){
        return new RowMapper<Role>() {
            @Override
            public Role mapRow(ResultSet rs, int i) throws SQLException {
                Role entity = new Role();

                entity.setId(rs.getLong("id"));
                entity.setDeptId(rs.getLong("iDeptId"));
                entity.setRole(rs.getInt("tiRole"));
                entity.setSpecialtyId(rs.getLong("iSpecialtyId"));
                entity.setTeacherId(rs.getLong("iTeacherId"));

                return entity;
            }
        };
    }
}
